const mysql = require('mysql2/promise');

// 数据库配置
const dbConfig = {
    host: '43.143.253.188',
    port: 3306,
    user: 'nextjs',
    password: 'yEAiRE2jcxTyRBy5',
    database: 'nextjs',
    charset: 'utf8mb4'
};

async function createCharacterLocationsTable() {
    let connection;

    try {
        // 连接数据库
        connection = await mysql.createConnection(dbConfig);
        console.log('✅ 数据库连接成功');

        // 创建玩家位置表
        const createTableSQL = `
      CREATE TABLE IF NOT EXISTS \`character_locations\` (
        \`id\` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID',
        \`character_id\` bigint NOT NULL COMMENT '角色ID',
        \`area_id\` int NOT NULL DEFAULT 2 COMMENT '当前区域ID（默认2-新手村）',
        \`last_move_time\` datetime NOT NULL COMMENT '最后移动时间',
        \`is_online\` tinyint(1) NOT NULL DEFAULT 1 COMMENT '是否在线（1:在线, 0:离线）',
        \`last_online_time\` datetime NOT NULL COMMENT '最后在线时间',
        \`created_at\` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
        \`updated_at\` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
        PRIMARY KEY (\`id\`),
        UNIQUE KEY \`uk_character_id\` (\`character_id\`),
        KEY \`idx_area_id\` (\`area_id\`),
        KEY \`idx_is_online\` (\`is_online\`),
        KEY \`idx_last_online_time\` (\`last_online_time\`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='玩家位置表';
    `;

        await connection.execute(createTableSQL);
        console.log('✅ 玩家位置表创建成功');

        // 为现有角色创建默认位置记录
        const insertDefaultLocationsSQL = `
      INSERT IGNORE INTO character_locations (character_id, area_id, last_move_time, is_online, last_online_time)
      SELECT
        id as character_id,
        2 as area_id,
        NOW() as last_move_time,
        0 as is_online,
        NOW() as last_online_time
      FROM characters
      WHERE id NOT IN (SELECT character_id FROM character_locations);
    `;

        const [result] = await connection.execute(insertDefaultLocationsSQL);
        console.log(`✅ 为 ${result.affectedRows} 个角色创建了默认位置记录`);

        // 验证表结构
        const [rows] = await connection.execute('DESCRIBE character_locations');
        console.log('\n📋 表结构验证:');
        rows.forEach(row => {
            console.log(`  ${row.Field}: ${row.Type} ${row.Null === 'NO' ? 'NOT NULL' : 'NULL'} ${row.Default ? `DEFAULT ${row.Default}` : ''} ${row.Comment ? `COMMENT '${row.Comment}'` : ''}`);
        });

        // 统计数据
        const [countResult] = await connection.execute('SELECT COUNT(*) as total FROM character_locations');
        console.log(`\n📊 数据统计: 总共有 ${countResult[0].total} 条位置记录`);

        // 按区域统计
        const [areaStats] = await connection.execute(`
      SELECT 
        area_id,
        COUNT(*) as player_count,
        SUM(is_online) as online_count
      FROM character_locations 
      GROUP BY area_id
      ORDER BY area_id
    `);

        console.log('\n🗺️ 区域玩家分布:');
        areaStats.forEach(stat => {
            console.log(`  区域 ${stat.area_id}: 总玩家 ${stat.player_count}, 在线 ${stat.online_count}`);
        });

        console.log('\n🎉 玩家位置表创建和初始化完成！');

    } catch (error) {
        console.error('❌ 创建玩家位置表失败:', error);
        throw error;
    } finally {
        if (connection) {
            await connection.end();
            console.log('🔌 数据库连接已关闭');
        }
    }
}

// 运行脚本
if (require.main === module) {
    createCharacterLocationsTable()
        .then(() => {
            console.log('✅ 脚本执行完成');
            process.exit(0);
        })
        .catch((error) => {
            console.error('❌ 脚本执行失败:', error);
            process.exit(1);
        });
}

module.exports = { createCharacterLocationsTable };
